//
//  DBManager.m
//  ModelContrast
//
//  Created by Cluy on 15/7/14.
//  Copyright (c) 2015年 sunflowers. All rights reserved.
//

#import "DBManager.h"
@interface DBManager ()
- (NSString *)getPath:(NSString *)dbName;
@end
@implementation DBManager

- (id)initWithDBName:(NSString *)dbName
{
    
    self = [super init];
    
    if(nil != self)
    {
        _DBName = [self getPath:dbName];
        NSLog(@"DBName: %@", _DBName);
    }
    
    return self;
}
// 数据库存储路径(内部使用)
- (NSString *)getPath:(NSString *)dbName
{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    return [documentsDirectory stringByAppendingPathComponent:dbName];
}

// 能否打开数据库
- (BOOL)readyDatabse
{
    _DB = [FMDatabase databaseWithPath:_DBName];

    if (![_DB open])
    {
        [_DB close];
        NSAssert1(0, @"Failed to open database file with message '%@'.", [_DB lastErrorMessage]);
        return NO;
    }
    
    //为数据库设置缓存，提高查询效率
    [_DB setShouldCacheStatements:YES];
    return YES;
}

- (void)openDB
{
    [_DB open];
}
- (void)closeDB
{
    [_DB close];
}
#pragma mark 删除数据库
// 删除数据库
- (void)deleteDatabse
{
    BOOL success;
    NSError *error;
    
    NSFileManager *fileManager = [NSFileManager defaultManager];
    
    // delete the old db.
    if ([fileManager fileExistsAtPath:_DBName])
    {
        [_DB close];
        success = [fileManager removeItemAtPath:_DBName error:&error];
        if (!success) {
            NSAssert1(0, @"Failed to delete old database file with message '%@'.", [error localizedDescription]);
        }
    }
}

// 判断是否存在表
- (BOOL) isTableOK:(NSString *)tableName
{
    FMResultSet *rs = [_DB executeQuery:@"SELECT count(*) as 'count' FROM sqlite_master WHERE type ='table' and name = ?", tableName];
    while ([rs next])
    {
        // just print out what we've got in a number of formats.
        NSInteger count = [rs intForColumn:@"count"];
//        NSLog(@"isTableOK %ld", (long)count);
        
        if (0 == count)
        {
            return NO;
        }
        else
        {
            return YES;
        }
    }
    
    return NO;
}

// 获得表的数据条数
- (NSInteger) getTableItemCount:(NSString *)tableName
{
    NSString *sqlstr = [NSString stringWithFormat:@"SELECT count(*) as 'count' FROM %@", tableName];
    FMResultSet *rs = [_DB executeQuery:sqlstr];
    while ([rs next])
    {
        // just print out what we've got in a number of formats.
        NSInteger count = [rs intForColumn:@"count"];
        NSLog(@"TableItemCount %ld", (long)count);
        
        return count;
    }
    
    return 0;
}

// 创建表
- (BOOL) createTable:(NSString *)tableName fields:(NSArray *)fields
{
   
    if (fields && [fields count]>0) {
        NSString *sqlstr = [NSString stringWithFormat:@"CREATE TABLE %@ (%@)", tableName, [self listOfColumns:fields]];
        BOOL res = [_DB executeUpdate:sqlstr];
        return res;
    }
    return NO;
}

// 删除表
- (BOOL) deleteTable:(NSString *)tableName
{
    NSString *sqlstr = [NSString stringWithFormat:@"DROP TABLE %@", tableName];
    if (![_DB executeUpdate:sqlstr])
    {
        NSLog(@"Delete table error!");
        return NO;
    }
    
    return YES;
}

// 清除表
- (BOOL) eraseTable:(NSString *)tableName
{
    NSString *sqlstr = [NSString stringWithFormat:@"DELETE FROM %@", tableName];
    if (![_DB executeUpdate:sqlstr])
    {
        NSLog(@"Erase table error!");
        return NO;
    }
    
    return YES;
}
//插入数据
- (BOOL)insertTable:(NSString*)tableName  withArr:(NSArray *)sqlParam
{
    if(!tableName || [tableName isEqualToString:@""] || !sqlParam || [sqlParam count]<=0){
        return NO;
    }
    
    NSString *valueStr = [self listOfInsert:sqlParam];
    
    NSString *sqlstr =[NSString stringWithFormat:@"INSERT INTO %@  VALUES(%@)",tableName,valueStr];
    if (![_DB executeUpdate:sqlstr])
    {
        NSLog(@"数据库插入数据失败");
        return NO;
    }
    
    return YES;
}

//查询数据
- (NSArray *)selectFromTable:(NSString*)tableName fields:(NSArray*)fields whereParam:(NSString *)whereStr
                groupBy:(NSArray*)groupByParam orderby:(NSArray*)orderbyParam desc:(BOOL)desc{
    if(!tableName || [tableName isEqualToString:@""]){
        return nil;
    }
    NSMutableString *sqlstr =[NSMutableString stringWithFormat:@"SELECT %@ FROM %@",[self listOfselect:fields],tableName];
    if (whereStr && ![whereStr isEqualToString:@""]) {
        [sqlstr appendString:[NSString stringWithFormat:@" WHERE %@",whereStr]];
    }
    if (groupByParam && groupByParam !=nil) {
        [sqlstr appendString:[NSString stringWithFormat:@" GROUP BY %@",[self listOfselect:groupByParam]]];
    }
    if (orderbyParam && orderbyParam !=nil) {
        [sqlstr appendString:[NSString stringWithFormat:@" ORDER BY %@",[self listOfselect:orderbyParam]]];
        if (desc) {
            [sqlstr appendString:@" DESC"];
        }else{
            [sqlstr appendString:@" ASC"];
        }
    }
    
    FMResultSet *result = [_DB executeQuery:sqlstr];
    NSMutableArray *resultArr = [[NSMutableArray alloc]init];
    while ([result next]) {
//        NSLog(@"result = %@",[result resultDictionary]);
        [resultArr addObject:[result resultDictionary]];
    }
    return resultArr;
}
// 修改数据
- (BOOL)updateTable:(NSString*)sql, ...
{
    va_list args;
    va_start(args, sql);
    BOOL result = [_DB executeUpdate:sql];
    
    va_end(args);
    return result;
}
//插入拆分
- (NSString *) listOfInsert:(NSArray *)columnNames
{
    if (columnNames != nil)
    {
        NSMutableArray *columnNamesTemp = [[NSMutableArray alloc]init];
        for(int i=0;i<[columnNames count];i++){
            NSString *tempStr = [NSString stringWithFormat:@"\'%@\'",[columnNames objectAtIndex:i]];
            [columnNamesTemp addObject:tempStr];
        }
        return [columnNamesTemp componentsJoinedByString:@","];
    }
    else
    {
        return @"";
    }
}
//创建表拆分
- (NSString *)listOfColumns:(NSArray *)columnNames
{
    if (columnNames != nil)
    {
        NSMutableArray *columnNamesTemp = [[NSMutableArray alloc]init];
        for(int i=0;i<[columnNames count];i++){
            NSString *tempStr = [NSString stringWithFormat:@"%@ text",[columnNames objectAtIndex:i]];
            [columnNamesTemp addObject:tempStr];
        }
        return [columnNamesTemp componentsJoinedByString:@","];

    }
    else
    {
        return @"";
    }
}
//查询拆分
- (NSString *)listOfselect:(NSArray *)columnNames
{
    if (columnNames != nil)
    {
        return [columnNames componentsJoinedByString:@","];
    }
    else
    {
        return @"*";
    }
}

@end
